using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using Widgetsphere.Core.Exceptions;
using DbHelp = Widgetsphere.Core.DataAccess.DatabaseHelper;

namespace Widgetsphere.Core.DataAccess
{
	/// <summary>
	/// Summary description for APSDataTable.
	/// </summary>
	[System.ComponentModel.DesignerCategoryAttribute("code")]
	public abstract partial class PersistableDomainCollectionBase : DomainCollectionBase
	{

		#region Abstract Methods
		protected abstract string InsertStoredProcedureName { get; }
		protected abstract string UpdateStoredProcedureName { get; }
		protected abstract string DeleteStoredProcedureName { get; }
		protected abstract string InsertSQLText { get; }
		protected abstract string UpdateSQLText { get; }
		protected abstract string DeleteSQLText { get; }
		protected abstract bool UseStoredProcedure { get; }
		protected abstract List<IDbDataParameter> GetInsertFreeSqlParameters();
		protected abstract List<IDbDataParameter> GetUpdateFreeSqlParameters();
		protected abstract List<IDbDataParameter> GetDeleteFreeSqlParameters();
		public abstract bool HandleErrors();
		public abstract void CreateRelations();
		public abstract void SetChildSelectedFalse();
		#endregion

		protected internal void FullUpdate()
		{
			var conn = DbHelp.GetConnection(ConnectionString);
			conn.Open();
			var trans = conn.BeginTransaction();
			try
			{
				SetPersistedFalse();
				DeleteDomain(conn, trans, this);
				SetPersistedFalse();
				UpdateInsertDomain(conn, trans, this);
				trans.Commit();
				this.DataSet.AcceptChanges();
			}
			catch (Exception ex)
			{
				trans.Rollback();
				throw ex;
			}
			finally
			{
				conn.Close();
			}
		}

		private bool mPersisted = false;
		private void SetPersistedFalse()
		{
			foreach (PersistableDomainCollectionBase pe in this.DataSet.Tables)
			{
				pe.mPersisted = false;
			}
		}


		private static void UpdateInsertDomain(IDbConnection conn, IDbTransaction trans, PersistableDomainCollectionBase pe)
		{
			if (!pe.mPersisted)
			{
				foreach (DataRelation parentRel in pe.ParentRelations)
				{
					var parent = (PersistableDomainCollectionBase)parentRel.ParentTable;
					var relationOk = false;
					if (parent.IsChild(pe))
					{
						foreach (var dc in parentRel.ChildColumns)
						{
							if (dc.AllowDBNull)
								relationOk = true;
						}
					}
					if (!parent.mPersisted && !relationOk)
					{
						UpdateInsertDomain(conn, trans, parent);
					}
				}

				if (!pe.mPersisted)
				{
					pe.DoInserts(conn, trans);
					pe.DoUpdates(conn, trans);
					pe.mPersisted = true;
					foreach (DataRelation childRel in pe.ChildRelations)
					{
						var child = (PersistableDomainCollectionBase)childRel.ChildTable;
						UpdateInsertDomain(conn, trans, child);
					}
				}
			}
			foreach (PersistableDomainCollectionBase persistable in pe.DataSet.Tables)
			{
				if (!persistable.mPersisted)
				{
					UpdateInsertDomain(conn, trans, persistable);
				}
			}

		}

		private bool IsChild(PersistableDomainCollectionBase pe)
		{
			var IsChild = false;
			foreach (DataRelation childRel in pe.ChildRelations)
			{
				var child = (PersistableDomainCollectionBase)childRel.ChildTable;
				if (this == child)
				{
					IsChild = true;
				}
			}
			return IsChild;
		}


		private bool IsParent(PersistableDomainCollectionBase pe)
		{
			var IsParent = false;
			foreach (DataRelation parentRel in pe.ParentRelations)
			{
				var parent = (PersistableDomainCollectionBase)parentRel.ParentTable;
				if (this == parent)
				{
					IsParent = true;
				}
			}
			return IsParent;
		}

		private static void DeleteDomain(IDbConnection conn, IDbTransaction trans, PersistableDomainCollectionBase pe)
		{
			if (!pe.mPersisted)
			{
				foreach (DataRelation childRel in pe.ChildRelations)
				{
					var child = (PersistableDomainCollectionBase)childRel.ChildTable;
					var relationOk = false;

					if (child.IsParent(pe))
					{
						foreach (var dc in childRel.ChildColumns)
						{
							if (dc.AllowDBNull)
								relationOk = true;
						}
					}
					if (!child.mPersisted && !relationOk)
					{
						DeleteDomain(conn, trans, child);
					}
				}
				if (!pe.mPersisted)
				{
					pe.DoDeletes(conn, trans);
					pe.mPersisted = true;
					foreach (DataRelation parentRel in pe.ParentRelations)
					{
						var parent = (PersistableDomainCollectionBase)parentRel.ParentTable;
						DeleteDomain(conn, trans, parent);
					}
				}
			}
			foreach (PersistableDomainCollectionBase persistable in pe.DataSet.Tables)
			{
				if (!persistable.mPersisted)
				{
					DeleteDomain(conn, trans, persistable);
				}
			}
		}

		#region Internal Persistance Calls
		internal void FullUpdate(IDbConnection conn, IDbTransaction trans)
		{
			var da = DbHelp.GetDataAdapter();
			da.InsertCommand = GetInsertCommand(true);
			da.UpdateCommand = GetUpdateCommand(true);
			da.DeleteCommand = GetDeleteCommand(true);
			DbHelp.SetConnections(da, conn);
			DbHelp.SetTransaction(da, trans);
			DbHelp.Update(da, this);
		}
		internal virtual void DoUpdates(IDbConnection conn, IDbTransaction trans)
		{
			var al = new ArrayList();
			foreach (DataRow dr in this.Rows)
			{
				if (dr.RowState == DataRowState.Modified)
				{
					al.Add(dr);
				}
			}
			if (al.Count > 0)
			{
				var updates = (DataRow[])al.ToArray(typeof(DataRow));
				foreach (IPersistableDomainObject po in updates)
				{
					po.OnBeforeUpdate(po, new System.EventArgs());
				}
				var da = DbHelp.GetDataAdapter();
				this.SetTableMappings(ref da);
				da.UpdateCommand = this.GetUpdateCommand(true);
				DbHelp.SetConnections(da, conn);
				DbHelp.SetTransaction(da, trans);
				DbHelp.Update(da, updates, DataRowState.Modified);
				foreach (IPersistableDomainObject po in updates)
				{
					po.OnAfterUpdate(po, new System.EventArgs());
				}
			}
		}
		internal void DoInserts(IDbConnection conn, IDbTransaction trans)
		{
			var al = new ArrayList();
			foreach (DataRow dr in this.Rows)
			{
				if (dr.RowState == DataRowState.Added)
				{
					al.Add(dr);
				}
			}
			if (al.Count > 0)
			{
				var inserts = (DataRow[])al.ToArray(typeof(DataRow));
				foreach (IPersistableDomainObject po in inserts)
				{
					po.OnBeforeInsert(po, new System.EventArgs());
				}
				var da = DbHelp.GetDataAdapter();
				this.SetTableMappings(ref da);
				da.InsertCommand = this.GetInsertCommand(true);
				DbHelp.SetConnections(da, conn);
				DbHelp.SetTransaction(da, trans);
				DbHelp.Update(da, inserts, DataRowState.Added);
				foreach (IPersistableDomainObject po in inserts)
				{
					po.OnAfterInsert(po, new System.EventArgs());
				}

			}
		}
		internal void DoDeletes(IDbConnection conn, IDbTransaction trans)
		{
			var al = new ArrayList();
			foreach (DataRow dr in this.Rows)
			{
				if (dr.RowState == DataRowState.Deleted)
				{
					al.Add(dr);
				}
			}
			if (al.Count > 0)
			{
				var deletes = (DataRow[])al.ToArray(typeof(DataRow));
				var da = DbHelp.GetDataAdapter();
				this.SetTableMappings(ref da);
				da.DeleteCommand = this.GetDeleteCommand(true);
				DbHelp.SetConnections(da, conn);
				DbHelp.SetTransaction(da, trans);
				foreach (IPersistableDomainObject po in deletes)
				{
					po.OnBeforeDelete(po, new System.EventArgs());
				}
				DbHelp.Update(da, deletes, DataRowState.Deleted);
				foreach (IPersistableDomainObject po in deletes)
				{
					po.OnAfterDelete(po, new System.EventArgs());
				}

			}
		}
		#endregion

		#region Protected Persistance Calls
		protected IDbCommand GetInsertCommand(bool dataSetCommand)
		{
			IDbCommand returnVal = null;
			if (this.UseStoredProcedure)
			{
				var sdBase = (SubDomainBase)this.DataSet;
				var sp = StoredProcedureFactory.GetStoredProcedure(sdBase.ConnectionString, this.InsertStoredProcedureName);
				if (dataSetCommand) returnVal = sp.GetDataSetCommand();
				else returnVal = sp.GetDirectQueryCommand();
				return returnVal;
			}
			else
			{
				var sdBase = (SubDomainBase)this.DataSet;
				var sqlFreeText = new SQLFreeText(sdBase.ConnectionString, this.InsertSQLText);
				sqlFreeText.Parameters.AddRange(this.GetInsertFreeSqlParameters());
				returnVal = sqlFreeText.GetCommand();
				return returnVal;
			}
		}

		protected virtual IDbCommand GetUpdateCommand(bool dataSetCommand)
		{
			IDbCommand returnVal = null;
			if (this.UseStoredProcedure)
			{
				var sdBase = (SubDomainBase)this.DataSet;
				var sp = StoredProcedureFactory.GetStoredProcedure(sdBase.ConnectionString, this.UpdateStoredProcedureName);
				if (dataSetCommand) returnVal = sp.GetDataSetCommand();
				else returnVal = sp.GetDirectQueryCommand();
				return returnVal;
			}
			else
			{
				var sdBase = (SubDomainBase)this.DataSet;
				var sqlFreeText = new SQLFreeText(sdBase.ConnectionString, this.UpdateSQLText);
				sqlFreeText.Parameters.AddRange(this.GetUpdateFreeSqlParameters());
				returnVal = sqlFreeText.GetCommand();
				return returnVal;
			}			
		}

		protected IDbCommand GetDeleteCommand(bool dataSetCommand)
		{
			IDbCommand returnVal = null;
			if (this.UseStoredProcedure)
			{
				var sdBase = (SubDomainBase)this.DataSet;
				var sp = StoredProcedureFactory.GetStoredProcedure(sdBase.ConnectionString, this.DeleteStoredProcedureName);
				if (dataSetCommand) returnVal = sp.GetDataSetCommand();
				else returnVal = sp.GetDirectQueryCommand();
				return returnVal;
			}
			else
			{
				var sdBase = (SubDomainBase)this.DataSet;
				var sqlFreeText = new SQLFreeText(sdBase.ConnectionString, this.DeleteSQLText);
				sqlFreeText.Parameters.AddRange(this.GetDeleteFreeSqlParameters());
				returnVal = sqlFreeText.GetCommand();
				return returnVal;
			}
		}

		protected void ExecuteCommand(IDbCommand command, Hashtable parameterValues)
		{
			foreach (string column in parameterValues.Keys)
			{
				SetParameterValue(command, column, parameterValues[column]);
			}
			ExecuteNonQuery(command);
		}

		protected void ExecuteNonQuery(IDbCommand command)
		{
			if (command.Connection == null)
			{
				command.Connection = DbHelp.GetConnection(ConnectionString);
				command.Connection.Open();
				try
				{
					var effectedRows = DbHelp.ExecuteNonQuery(command);
					if (effectedRows == 0)
					{
						throw new System.Data.DBConcurrencyException(command.CommandText);
					}
				}
				catch (Exception ex)
				{
					throw ex;
				}
				finally
				{
					command.Connection.Close();
				}
			}
			else
			{
				var effectedRows = DbHelp.ExecuteNonQuery(command);
				if (effectedRows == 0)
				{
					throw new System.Data.DBConcurrencyException(command.CommandText);
				}
			}
		}

		#endregion

		#region Constraints
		protected void ConstraintUnique(DataColumn dc, bool unique)
		{
			try
			{
				dc.Unique = unique;
			}
			catch (Exception ex)
			{
				throw new UniqueConstraintViolatedException(dc.ColumnName + " value " + unique.ToString() + " - unique constraint failed.", ex);
			}
		}
		protected void ConstraintNullable(DataColumn dc, bool nullable)
		{
			try
			{
				dc.AllowDBNull = nullable;
			}
			catch (Exception ex)
			{
				throw new UniqueConstraintViolatedException(dc.ColumnName + " value " + nullable.ToString() + " - nullable constraint failed.", ex);
			}
		}
		protected void ConstraintMaxLength(DataColumn dc, int maxLength)
		{
			try
			{
				dc.MaxLength = maxLength;
			}
			catch (Exception ex)
			{
				throw new UniqueConstraintViolatedException(dc.ColumnName + " value " + maxLength.ToString() + " - max length constraint failed.", ex);
			}
		}
		protected void ConstraintReadOnly(DataColumn dc, bool readOnly)
		{
			try
			{
				dc.ReadOnly = readOnly;
			}
			catch (Exception ex)
			{
				throw new UniqueConstraintViolatedException(dc.ColumnName + " value " + readOnly.ToString() + " - read only constraint failed.", ex);
			}
		}
		protected void ConstraintDefaultValue(DataColumn dc, object defaultValue)
		{
			try
			{
				dc.DefaultValue = defaultValue;
			}
			catch (Exception ex)
			{
				throw new UniqueConstraintViolatedException(dc.ColumnName + " value " + defaultValue.ToString() + " - default value setup failed.", ex);
			}
		}
		protected void ConstraintDataType(DataColumn dc, Type dataType)
		{
			if (dc.DataType != dataType)
			{
				throw new UniqueConstraintViolatedException(dc.ColumnName + " value " + dataType.ToString() + " - data type constraint failed.");
			}
		}
		#endregion

		#region Other

		protected void ImportDataTable(DataTable dt)
		{
			foreach (DataColumn dc in dt.Columns)
			{
				this.Columns.Add(dc.ColumnName, dc.DataType, dc.Expression);
			}
			foreach (DataRow dr in dt.Rows)
			{
				this.ImportRow(dr);
			}
		}

		public static void SetParameterValue(IDbCommand command, string paramName, object inValue)
		{
			try
			{
				var startDateParam = (IDbDataParameter)command.Parameters[paramName];
				if (startDateParam != null)
				{
					startDateParam.Value = inValue;
				}
				else
				{
					throw new WSException("Parameter: " + paramName + " is missing from stored procedure: " + command.CommandText);
				}
			}
			catch (Exception ex)
			{
				throw new WSException("Parameter: " + paramName + " is missing from stored procedure: " + command.CommandText, ex);
			}
		}

		public static IDbDataParameter GetOutputParameter(IDbCommand command, string paramName)
		{
			try
			{
				var outputParam = (IDbDataParameter)command.Parameters[paramName];
				if (outputParam == null)
				{
					throw new WSException("Parameter: " + paramName + " is missing from stored procedure: " + command.CommandText);
				}
				return outputParam;
			}
			catch (Exception ex)
			{
				throw new WSException("Parameter: " + paramName + " is missing from stored procedure: " + command.CommandText, ex);
			}
		}
		#endregion

	}
}
